<?php
namespace Qgs\Controller;

use Qgs\Controller\BaseController;
use Common\Model\BillsExportModel;

class BillsController extends BaseController
{

    public function __construct()
    {
        parent::__construct();
        $this->local_id = session('userInfo')['local_id'];
        if (!$this->local_id) $this->local_id = 1;
    }

    public function index()
    {

        $p = max(I('param.p'), 1);
        $num = max(I('param.num'), 20);
        $this->assign('page', $p);
        $this->assign('num', $num);

        $order_field = I('param.order_field', 'id');
        $order = I('param.order', 'desc');
        $sunxu = $order == 'asc' ? 'desc' : 'asc';
        $this->assign('order_field', $order_field);
        $this->assign('order', $sunxu);
        $order = array($order_field => $order);

        $state = I('get.state');//结算状态

        $search_field = I('param.search_field');
        $search = trim(I('param.search'));

        $where[] = '1=1';
        $where[] = ['local_id' => $this->local_id];
        if ($search && $search_field) {
            $where[] = $search_field . " like '%" . $search . "%'";
            $this->assign('search', $search);
            $this->assign('search_field', $search_field);
        }
        $count = M('b2c_bills_account')
            ->where($where)
            ->count();
        $Page = new \Think\Page($count, $num);
        $show = $Page->show();
        $data = M('b2c_bills_account')
            ->page($p, $num)
            ->where($where)
            ->order($order)
            ->select();
        //echo M()->_sql();
        $this->assign('list', $data);
        $this->assign('page', $show);
        $this->display();
    }

    public function view()
    {

        $id = $id ? $id : I('get.id', '', 'intval');
        if (!$id) $this->error('id缺失');
        $bill_model = new BillsExportModel();
        $bill_model->id = $id;
        $res = $bill_model->view();
        $this->assign($res);
        $this->display('Admin@Bills/view');
    }

    /**
     * 确认对账成功或者是解决异议
     */
    public function changeStatus()
    {
        $id = I('post.id');
        if (!IS_AJAX || !$id) {
            $this->ajaxReturn(['status' => false, 'message' => '非法请求', 'data' => []]);
        }
        $state = I('post.state', 0, 'intval');
        $res = M('b2c_bills_account')->where(['id' => $id])->save(['state' => (string)$state]);
        if ($res !== false) {
            $this->ajaxReturn(['status' => true, 'message' => '成功', 'data' => []]);
        } else {
            $this->ajaxReturn(['status' => false, 'message' => '失败了', 'data' => []]);
        }
    }

    /**
     * 提出异议 改变状态 异议留言
     */
    public function sendyiyi()
    {
        $id = I('post.id');
        if (!IS_AJAX || !$id) {
            $this->ajaxReturn(['status' => false, 'message' => '非法请求', 'data' => []]);
        }
        $name = I('post.name');
        $tel = I('post.tel');
        $desc = I('post.desc');
        $exists = M('b2c_bills_account')->where(['id' => $id])->getField('talk');
        if ($exists) {
            $talk = json_decode($exists, true);
            array_push($talk, [
                'name' => $name,
                'tel' => $tel,
                'desc' => $desc,
                'time' => time(),
                'role' => 'store',
            ]);
        } else {

            $talk[0] = [
                'name' => $name,
                'tel' => $tel,
                'desc' => $desc,
                'time' => time(),
                'role' => 'store',
            ];
        }
        $res = M('b2c_bills_account')->where(['id' => $id])->save(['state' => '2', 'talk' => json_encode($talk)]);
        if ($res !== false) {
            $this->ajaxReturn(['status' => true, 'message' => '成功', 'data' => []]);
        } else {
            $this->ajaxReturn(['status' => false, 'message' => '修改失败', 'data' => []]);
        }
    }

    /**
     * 导出结算单
     */
    public function exportExcel()
    {
        $id = I('get.id');
        if (!$id) $this->error('id缺失');

        $exp = new BillsExportModel();
        $exp->id = $id;
        $exp->exportExcel();
    }



    public function excel($expCellName, $expTableData)
    {
        $xlsTitle = iconv('utf-8', 'gb2312', '怡臣电子商务结算单');//文件名称
        $fileName = date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        Vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new \PHPExcel();
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q');
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle . '  导出时间:' . date('Y-m-d H:i:s'));
        for ($i = 0; $i < $cellNum; $i++) {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
        }
        // Miscellaneous glyphs, UTF-8
        /*for($i=0;$i<$dataNum;$i++){
            for($j=0;$j<$cellNum;$j++){
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][2]]);
            }
        }*/
        //循环行
        foreach ($expTableData as $hang => $value) {
            //循环列
            $i = 0;

            if ($value['geshu'] > 1) {
                $i = 0;
                foreach ($value as $lie => $vv) {
                    if ($lie == 'list') {
                        foreach ($vv as $dd) {
                            if (!isset($z) || $z > $hang + 3 + $value['geshu']) $z = $hang + 3;
                            foreach ($dd as $e => $ee) {
                                if (!isset($y) || $y > 5) $y = 2;
                                //var_dump($cellName[$y].($z),$ee);
                                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$y] . ($z), $ee);
                                $y++;
                            }
                            $z++;
                        }
                        $i += 4;
                    } elseif ($lie == 'geshu') {

                    } else {
                        //dump($i);
                        $objPHPExcel->getActiveSheet(0)->mergeCells($cellName[$i] . ($hang + 3) . ':' . $cellName[$i] . ($hang + 2 + $value['geshu']));//合并单元格
                        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . ($hang + 3), $vv);
                        $i++;
                    }
                }
            } else {
                $i = 0;
                //var_dump($cellName[$i].($hang+3), $value['id']);
                foreach ($value as $l => $v) {
                    if ($l == 'list') {
                        foreach ($v[0] as $c) {
                            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . ($hang + 3), $c);
                            $i++;
                        }
                    } elseif ($l == 'geshu') {

                    } else {
                        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . ($hang + 3), $v);
                        $i++;
                    }
                }

            }

        }
//exit;
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    /**
     * 生成报表
     * 序 结算单号 结算日期 结算订单数量 应结算金额 退款金额 优惠金额 抽佣金额 线上手续费 银行手续费 实际结算 经办人 划账日期 备注说明 结算状态
     */
    public function createReport()
    {
        $start_time = I('get.start_time', 0);
        $end_time = I('get.end_time', 0);
        if ($start_time) {
            $start_time = strtotime($start_time);
        }
        if ($end_time) {
            $end_time = strtotime($end_time) + 86399;
        }
        $local_id = $this->local_id;
        $where = [
            'local_id' => $local_id,
            'create_time' => ['EGT', $start_time],
            'create_time' => ['ELT', $end_time],
        ];
        $field = [
            'id','account_id','create_time','order_num',
            'order_finalPirce','refund_finalPrice','order_youhui',
            'order_yongjin','order_other','order_bank',
            'final_accountMoney','handle_name','update_time',
            'remark','state'
        ];
        $list = M('b2c_bills_account')->where($where)->field($field)->select();

        $arr = ['结算中','已结算','异议处理中'];
        if ($list) {

            foreach ($list as $key=>&$item) {
                $item['id'] = $key+1;
                $item['account_id'] = ' '.$item['account_id'];
                $item['create_time'] = date('Y-m-d H:i:s', $item['create_time']);
                if ($item['update_time'] > 0) {
                    $item['update_time'] = date('Y-m-d H:i:s', $item['update_time']);
                }
                $item['state'] = $arr[$item['state']];
            }
        }
        $expCellName = [
            ['0', '序', 'id'], ['1', '结算单号', 'account_id'], ['2', '结算日期', 'create_time'], ['3', '结算订单数量', 'order_num'],
            ['4', '应结算金额', 'order_finalPirce'], ['5', '退款金额', 'refund_finalPrice'], ['6', '优惠金额', 'order_youhui'],
            ['7', '抽佣金额', 'order_yongjin'], ['8', '线上手续费', 'order_other'], ['9', '银行手续费', 'order_bank'],
            ['10', '实际结算', 'final_accountMoney'], ['11', '经办人', 'handle_name'], ['12', '划账日期', 'update_time'],
            ['13', '备注说明', 'remark'], ['14', '结算状态', 'state'],
        ];
        $start = date('Y年m月d日 H:i:s', $start_time);
        $end = date('Y年m月d日 H:i:s', $end_time);
        $dingdan = array_sum(array_column($list, 'order_num'));
        $jiesuan = count($list);
        $zongjine = array_sum(array_column($list, 'final_accountMoney'));
        $str = $start.' 至 '.$end.' 共结算'.$dingdan.'笔订单，产生'.$jiesuan.'次结算，实际结算总额为'.$zongjine.'。';
//        var_dump($expCellName, $list, $str);return;
        $this->excel3($expCellName, $list, $str);

    }

    public function excel3($expCellName, $expTableData, $str) {
        $xlsTitle = iconv('utf-8', 'gb2312', '怡臣电子商务结算单');//文件名称
        $fileName = date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);

        Vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new \PHPExcel();
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q');
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $str);
        for ($i = 0; $i < $cellNum; $i++) {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
        }
        if ($dataNum > 0) {
            //逻辑区
            for ($i = 0; $i < $dataNum; $i++) {
                for ($j = 0; $j < $cellNum; $j++) {
                    $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3), $expTableData[$i][$expCellName[$j][2]]);
                }
            }
        }

        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
}